package TopTen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * 
 * @author henrinijborg & jonatanprins
 * @version rc2
 *
 */
public class DatabaseStations{
	
	/*
	 * Declaration of variables
	 */
	private Connection conn;
	private Statement stmt;
	
	/** Server address 		*/
	private final static String 	SERVER_ADDRESS 	= 	"127.0.0.1";	
	/** Server port			*/
	private final static int		PORT			= 	3306;			
	/** Database user		*/
	private final static String 	USER			= 	"root";
	/** It's password		*/
	private final static String 	PWD				= 	"";		
	/** The database name	*/
	private final static String 	DB				= 	"unwdmi";		
	
	private HashMap<Integer, Integer> stations;
	
	/**
	 * Contstructor
	 */
	public DatabaseStations(){
		connect();
		fillStations();
	}
	
	/**
	 * this method connects to the database
	 */
	private void connect(){
		try
        {
            String url = "jdbc:mysql://" + SERVER_ADDRESS + ":" + PORT + "/" + DB;
            
            Class.forName ("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, USER, PWD);
            stmt = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
	}
	
	/**
	 * @return a HashMap with the country name and an ArrayList with all it's station numbers
	 * @throws Exception when it fails
	 */
	public HashMap<String, ArrayList<Integer>> getStationsByCountry() throws Exception {
		HashMap<String, ArrayList<Integer>> output = new HashMap<String, ArrayList<Integer>>();
		
		String queryCountry = "SELECT country FROM stations WHERE continent = 'Africa' OR continent = 'South America' GROUP BY country";
		ResultSet resultCountry = stmt.executeQuery(queryCountry);
		
		while(resultCountry.next()) {
			ArrayList<Integer> stations = new ArrayList<Integer>();
			
			String country = resultCountry.getString(1);
			String queryStations = "SELECT stn FROM stations WHERE country = \"" + country + "\"";
			stmt = conn.createStatement();
			ResultSet resultStations = stmt.executeQuery(queryStations);
			while (resultStations.next()) {				
				stations.add(resultStations.getInt(1));
			}
			
			output.put(country, stations);
		}
		
		return output;
	}
	
	/**
	 * Fills this.stations with all the station numbers from the DB for indexing
	 */
	public void fillStations(){
		stations = new HashMap<Integer, Integer>();
		int x = 0;		
		try {
			ResultSet result = stmt.executeQuery("SELECT stn FROM stations ORDER BY STN ASC");
			while(result.next()){
				stations.put(result.getInt(1), x);
				x++;
			}		
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
	}
	
	/**
	 * @return a HashMap with all the station numbers and their position for indexing
	 */
	public HashMap<Integer, Integer> getStations(){
		return stations;
	}
}